﻿Imports MySql.Data.MySqlClient

Public Class frmadd_container_one
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1

    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Public Shared idvoyage As Integer
    Public Shared datepenang As String
    Public Shared datethai As String
    Public Shared timehhmm As String
    Public Shared datetime As String
    Public Shared sumint As Double
    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader
    Dim idlast As String
    Dim respone As Object
    Private Sub frmadd_container_one_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        RadioButton6.Checked = True
        TextBox1.ReadOnly = False
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                With ListView1.Items.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("N")
                    .SubItems.Add(mySqlReader("VOYVESNAMEN"))
                    .SubItems.Add(mySqlReader("VOYDATESN"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("S")
                    .SubItems.Add(mySqlReader("VOYVESNAMES"))
                    .SubItems.Add(mySqlReader("VOYDATESS"))
                    .SubItems.Add(mySqlReader("VOYDATEES"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

    End Sub
    Public Sub searchData()
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage where VOYAGEID like '%" & TextBoxX1.Text & "%';"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("N")
                    .SubItems.Add(mySqlReader("VOYVESNAMEN"))
                    .SubItems.Add(mySqlReader("VOYDATESN"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("S")
                    .SubItems.Add(mySqlReader("VOYVESNAMES"))
                    .SubItems.Add(mySqlReader("VOYDATESS"))
                    .SubItems.Add(mySqlReader("VOYDATEES"))
                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Private Sub RadioButton6_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton6.CheckedChanged
        If RadioButton6.Checked = True Then
            TextBox1.ReadOnly = False
        Else
    
        End If
    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mySqlCommand = New MySqlCommand
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        respone = MsgBox("ยืนยันข้อมูลถูกต้อง", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then

            If RadioButton6.Checked = True Then
                If RadioButton4.Checked = True Then
                    Try
                        mysql.Close()
                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        Try
                            mySqlCommand.Parameters.Clear()
                            mySqlCommand.CommandText = "insert into ctnmain ( CTNSTRING, CTNSIZE, CTNAGENT, CTNSTAT,CTNCONSI,CTNVOYN) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn);  SELECT LAST_INSERT_ID()"
                            mySqlCommand.Connection = mysql
                            mySqlCommand.Parameters.AddWithValue("@ctnstring", txt_contain.Text)
                            If RadioButton1.Checked = True Then
                                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton1.Text)
                            ElseIf RadioButton2.Checked = True Then
                                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton2.Text)
                            End If

                            mySqlCommand.Parameters.AddWithValue("@ctnagent", TextBox3.Text)
                            mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                            mySqlCommand.Parameters.AddWithValue("@ctnconsi", " ")
                            mySqlCommand.Parameters.AddWithValue("@ctnvoyn", TextBox1.Text)

                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                



                ElseIf RadioButton3.Checked = True Then

                    Try
                        mysql.Close()
                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        Try
                            mySqlCommand.Parameters.Clear()
                            mySqlCommand.CommandText = "insert into ctnmain ( CTNSTRING, CTNSIZE, CTNAGENT, CTNSTAT,CTNCONSI,CTNVOYN) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn);  SELECT LAST_INSERT_ID()"
                            mySqlCommand.Connection = mysql
                            mySqlCommand.Parameters.AddWithValue("@ctnstring", txt_contain.Text)
                            If RadioButton1.Checked = True Then
                                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton1.Text)
                            ElseIf RadioButton2.Checked = True Then
                                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton2.Text)
                            End If

                            mySqlCommand.Parameters.AddWithValue("@ctnagent", TextBox3.Text)
                            mySqlCommand.Parameters.AddWithValue("@ctnstat", "0")
                            mySqlCommand.Parameters.AddWithValue("@ctnconsi", "DEPOT")
                            mySqlCommand.Parameters.AddWithValue("@ctnvoyn", TextBox1.Text)

                            idlast = mySqlCommand.ExecuteScalar()
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try
                        mySqlCommand.Parameters.Clear()
                        mySqlCommand.CommandText = "insert into ctndepot ( CTNID, CTNDATEIN, CTNSTATDEPOT, CTNDAYRE,CTNDEDATES) values (@ctnid,@ctndatein,@ctnstat,@ctndayre,@CTNDEDATES);"
                        mySqlCommand.Connection = mysql
                        mySqlCommand.Parameters.AddWithValue("@ctnid", idlast)
                        mySqlCommand.Parameters.AddWithValue("@ctndatein", "0")
                        mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                        mySqlCommand.Parameters.AddWithValue("@ctndayre", "0")

                        mySqlCommand.Parameters.AddWithValue("@CTNDEDATES", Date.Now.ToString("dd-MM-yyyy"))
                        mySqlCommand.ExecuteNonQuery()
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()




                End If

                mysql1.Close()
                If mysql1.State = ConnectionState.Closed Then
                    mysql1.Open()
                End If
                Try
                    mySqlCommand1.Parameters.Clear()
                    mySqlCommand1.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                    mySqlCommand1.Connection = mysql1
                    mySqlCommand1.Parameters.AddWithValue("@rptctn", txt_contain.Text)
                    mySqlCommand1.Parameters.AddWithValue("@rptstat", "MA")
                    mySqlCommand1.Parameters.AddWithValue("@rptctndate", datethai)
                    mySqlCommand1.Parameters.AddWithValue("@rptctndatetpe", datepenang)

                    If RadioButton2.Checked = True Then
                        mySqlCommand1.Parameters.AddWithValue("@rpttype", "45G1")
                    ElseIf RadioButton1.Checked = True Then
                        mySqlCommand1.Parameters.AddWithValue("@rpttype", "22G1")
                    End If


                    mySqlCommand1.Parameters.AddWithValue("@rptidctn", idlast)
                    mySqlCommand1.Parameters.AddWithValue("@rptagent", TextBox3.Text)
                    mySqlCommand1.Parameters.AddWithValue("@rptdatetype1", timehhmm)
                    mySqlCommand1.ExecuteNonQuery()
                    mysql1.Close()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

                'ElseIf RadioButton5.Checked = True And RadioButton3.Checked = True Then
                '    Try
                '        If mysql.State = ConnectionState.Closed Then
                '            mysql.Open()
                '        End If
                '        Try
                '            mySqlCommand.Parameters.Clear()
                '            mySqlCommand.CommandText = "insert into ctnout ( CTNSTRINGO, CTNAGENTO, CTNSIZEO, CTNCONDITIONO,CTNSEALNOO) values (@ctnstring,@ctnsize,@ctnagent,@ctnstat,@ctnconsi,@ctnvoyn);  SELECT LAST_INSERT_ID()"
                '            mySqlCommand.Connection = mysql
                '            mySqlCommand.Parameters.AddWithValue("@ctnstring", txt_contain.Text)
                '            If RadioButton1.Checked = True Then
                '                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton1.Text)
                '            ElseIf RadioButton2.Checked = True Then
                '                mySqlCommand.Parameters.AddWithValue("@ctnsize", RadioButton2.Text)
                '            End If

                '            mySqlCommand.Parameters.AddWithValue("@ctnagent", TextBox3.Text)
                '            mySqlCommand.Parameters.AddWithValue("@ctnstat", "1")
                '            mySqlCommand.Parameters.AddWithValue("@ctnconsi", " ")
                '            mySqlCommand.Parameters.AddWithValue("@ctnvoyn", TextBox1.Text)

                '        Catch ex As Exception
                '            MsgBox(ex.ToString)
                '        End Try
                '        mysql.Close()
                '    Catch ex As Exception
                '        MsgBox(ex.ToString)
                '    End Try

            End If

        End If
        mysql.Close()




    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        searchData()

    End Sub

    
    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
  
    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click

        idvoyage = ListView1.SelectedItems(0).SubItems(0).Text
        timehhmm = ListView1.SelectedItems(0).SubItems(4).Text
        DateTime = ListView1.SelectedItems(0).SubItems(5).Text
        Try
            stringDate = Split(timehhmm, "-")
            'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
            stringhour = Split(datetime, ":")
            'MsgBox(stringhour(0) + stringhour(1))
            sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)

            sumint = CDbl(sumtxt) + 100

            datethai = sumtxt + "00"
            datepenang = sumint.ToString + "00"

            TextBox1.Text = ListView1.SelectedItems(0).SubItems(0).Text
        Catch ex As Exception

        End Try



    End Sub

    Private Sub TextBoxX1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX1.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchData()

        End If
    End Sub
End Class